package com.goods.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.goods.model.Page;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.sql.*;
import java.util.*;

/**
 * 数据库链接工具类
 * @author huangyanzhong
 * @date 2020-04-01
 */
@Component
public class DBManager implements InitializingBean {

    private Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * 数据库地址
     */
    @Value("${spring.datasource.url}")
    private String url;

    /**
     * 数据库账号
     */
    @Value("${spring.datasource.username}")
    private String username;

    /**
     * 数据库密码
     */
    @Value("${spring.datasource.password}")
    private String password;

    /**
     *  初始链接数
     */
    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;

    /**
     * 最大活动链接数
     */
    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;

    /**
     * 最小闲置链接数
     */
    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;

    /**
     * 链接耗尽时获取新链接的最大等待时间
     */
    @Value("${spring.datasource.druid.max-wait}")
    private long maxWait;


    /**
     * 连接池
     */
    private DruidDataSource pool;

    /**
     * 初始化Bean时执行
     */
    @Override
    public void afterPropertiesSet(){
        init();
    }

    /**
     * 初始化
     */
    private void init(){
        pool = new DruidDataSource();
        pool.setUrl(url);
        pool.setUsername(username);
        pool.setPassword(password);
        pool.setInitialSize(initialSize);
        pool.setMaxActive(maxActive);
        pool.setMinIdle(minIdle);
        pool.setMaxWait(maxWait);
    }

    public Connection getConnection() {
        try {
            // 若连接池为空或者异常关闭，则重新初始化一个
            if (pool == null || pool.isClosed()) {
                init();
            }
            return pool.getConnection();
        } catch (SQLException e) {
            logger.error(e.toString());
        }
        return null;
    }

    /**
     * 执行插入、修改、删除SQL
     *
     * @param sql
     * @param params
     * @return
     */
    public boolean execute(String sql, Object... params) throws SQLException{
        Connection connection=getConnection();
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            int result=ps.executeUpdate();
            return  result> 0;
        } catch (SQLException e) {
            throw e;
        } finally {
            close(ps,null);
        }
    }

    /**
     * 查询
     * @param params
     * @return
     */
    public List<Map<String,Object>> select(String sql,Object... params) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Connection connection=getConnection();
        if(connection==null){
            return null;
        }
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setQueryTimeout(60*5);
            if(params!=null&&params.length>0){
                for(int i=0;i<params.length;i++){
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            List<Map<String,Object>> records=new ArrayList<Map<String, Object>>();
            ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
            int columnCount=resultSetMetaData.getColumnCount();
            List<String> columns=new ArrayList<String>();
            for(int i=1;i<=columnCount;i++) {
                columns.add(resultSetMetaData.getColumnName(i));
            }
            while (resultSet.next()) {
                Map<String,Object> record=new HashMap<String,Object>();
                for(String columnName:columns){
                    Object value = resultSet.getObject(columnName);
                    record.put(columnName.toLowerCase(),value);
                }
                records.add(record);
            }
            return records;
        }catch (SQLException e){
            throw new SQLException(e);
        }finally {
           close(preparedStatement,connection);
        }
    }

    /**
     * 分页查询
     * @param sql
     * @params params
     * @param page
     * @return
     * @throws SQLException
     */
    public List<Map<String,Object>> selectByPage(String sql, Object[] params, Page page) throws SQLException {
        String pageSelectSql =sql;
        if(page!=null){
            pageSelectSql = getPaginationSql(sql,page.getPage(),page.getRows());
        }
        List<Map<String, Object>> records = select(pageSelectSql,params);
        return records;
    }

    /**
     * 分页查询SQL
     * @param selectSql
     * @param pageNo
     * @param pageSize
     * @return
     */
    public String getPaginationSql(String selectSql, long pageNo, long pageSize){
        if(pageNo<=0){
            pageNo=1;
        }
        pageNo=pageNo-1;
        long start = pageNo * pageSize + 1;
        long end = (pageNo + 1) * pageSize;
        pageSize=end-start+1;
        String pageSelectSql = selectSql + " LIMIT " + (start - 1) + "," + pageSize;
        return pageSelectSql;
    }


    /**
     * 资源关闭
     *
     * @param stmt
     * @param connection
     */
    public void close(Statement stmt, Connection connection) {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            logger.error(e.toString());
        }
    }

    /**
     * 获取记录数
     * @return
     */
    public long getRowsCount(String sql,Object... params) throws SQLException{
        Object result=getSingleResult(sql,params);
        if(result==null){
            throw new SQLException("查询失败");
        }
        long rowCount = Long.parseLong(result.toString());
        return rowCount;
    }

    /**
     * 获取只有一行一列的结果集
     * @param params
     * @return
     * @throws SQLException
     */
    public Object getSingleResult(String sql,Object... params) throws SQLException{
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection connection=getConnection();
        if(connection==null){
            return null;
        }
        try {
            ps = connection.prepareStatement(sql);
            ps.setQueryTimeout(30*60);
            if(params!=null&&params.length>0){
                for(int i=0;i<params.length;i++){
                    ps.setObject(i+1,params[i]);
                }
            }
            rs=ps.executeQuery();
            if(rs.next()){
                Object result = rs.getObject(1);
                return result;
            }
        } catch (SQLException e) {
            throw new SQLException(e);
        }finally {
            close(ps,connection);
        }
        return null;
    }


}
